from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

# https://docs.sqlalchemy.org/en/14/orm/tutorial.html#connecting

# STEP1 连接数据库
user, password, host, database = None, None, None, None
# 数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名
database_url = f'mysql+pymysql://{user}:{password}@{host}/{database}'
# connect_str = f'oracle://{user}:{password}@{host}/{database}'

# URL <database_urls>
engine = create_engine(database_url)
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
db_session = DBSession()

# 增

# 删
db_session.query(TContractMain).filter(TContractMain.id == "id").delete()
# 查
db_session.query(TContractMain).filter(TContractMain.id == "contract_id").scalar()
# 多条件查询
db_session.query(TContractMain).filter(TContractMain.id == "contract_id").filter(
    TContractMain.contractcode == "contractcode").scalar()
# 改
db_session.query(TContractMain).filter(TContractMain.id == "contract_id").update({"activetime": "update_active_time"})
db_session.commit()

# Tips
# isolation_level="READ UNCOMMITTED" 设置隔离级别为READ UNCOMMITTED 这样可以查询到最新的数据
engine = create_engine(database_url, isolation_level="READ UNCOMMITTED")


# 输出 dict结构结果 键为字段名
res = db_session.execute(some_sql)
data = [dict(zip(result.keys(), result)) for result in res]